package com.example.home;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
//////
public class BDataBase {
    private static String jdbc_driver = "com.mysql.cj.jdbc.Driver";
    private static String jdbc_url = "jdbc:mysql://localhost:3306/mysql";
    private static String account = "root";
    private static String pwd  = "wwsh2416";
    private static String table = "test";
    private static void release_data(Connection connection,Statement statement){
        release_data(connection,statement,null);
    }
    private static void release_data(Connection connection,Statement statement,ResultSet resultSet){
        if (connection != null){
            try {
                connection.close();
            }catch (SQLException error){
                error.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            }catch (SQLException error){
                error.printStackTrace();
            }
        }
        if (resultSet != null){
            try {
                resultSet.close();
            }catch (SQLException error){
                error.printStackTrace();
            }
        }
    }
    public static Connection connect_database() throws Exception{
        Connection connection = null;
        try {
             Class.forName(jdbc_driver); // 注册jdbc驱动
             connection = DriverManager.getConnection(jdbc_url,account,pwd);
        } catch (ClassNotFoundException e) {
            throw new Exception(e);
        } catch (SQLException e) {
            throw new Exception(e);
        }
        return connection;
    }
    public static ArrayList<Map<String,Object>> search_data_id(String user_id) throws Exception{
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        ArrayList<Map<String,Object>> list_data = new ArrayList<>();
        String sql = String.format("SELECT * FROM %s WHERE id = %s",table,user_id);
        try {
            connection = BDataBase.connect_database();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                HashMap<String,Object> map = decode_data(resultSet);
                list_data.add(map);
            }
        } catch (SQLException e) {
            throw new Exception(e);
        }finally {
            release_data(connection,statement,resultSet);
        }
        return list_data;
    }
    public static ArrayList<Map<String,Object>> search_data(int page,int size) throws Exception{
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        ArrayList<Map<String,Object>> list_data = new ArrayList<>();
        String sql = String.format("SELECT * FROM %s LIMIT %d,%d",table,page,size);
        try {
            connection = BDataBase.connect_database();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                HashMap<String,Object> map = decode_data(resultSet);
                list_data.add(map);
            }
        } catch (SQLException e) {
            throw  new Exception(e);
        }finally {
            release_data(connection,statement,resultSet);
        }
        return list_data;
    }
    public  static boolean insert_data(String names,int ages,float heights,String phones) throws Exception{
        Connection connection = null;
        Statement statement = null;
        int rs = 0;
        String sql = String.format("insert into test (age,name,height,phone) values(%d,'%s',%.1f,%s)",ages,names,heights,phones);
        try {
            connection = connect_database();
            connection.setAutoCommit(false);//关闭自动提交
            statement = connection.createStatement();
            rs = statement.executeUpdate(sql);
            connection.commit();//提交事务
        } catch (SQLException e) {
            connection.rollback();//回滚
            throw new Exception(e);
        }finally {
            connection.setAutoCommit(true);//开启自动提交
            release_data(connection,statement);
        }
        return rs == 1 ? true : false;
    }
    public static Boolean update_data(String user_id,String name) throws Exception{
        Connection connection = null;
        Statement statement = null;
        int result = 0;
        String sql = String.format("update %s set name = '%s' where id = %s",table,name,user_id);
        try {
            connection = connect_database();
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            result = statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw new Exception(e);
        }finally {
            connection.setAutoCommit(true);
            release_data(connection,statement);
        }
        return result == 1 ? true : false;
    }
    public  static boolean  delete_data(String user_id) throws Exception{
        Connection connection = null;
        Statement statement = null;
        int rs = 0;
        String sql = String.format("delete from %s where id = %s",table,user_id);
        try {
            connection = connect_database();
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            rs = statement.executeUpdate(sql);
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw  new Exception(e);
        }finally {
            connection.setAutoCommit(true);
            release_data(connection,statement);
        }
        return rs == 1 ? true : false;
    }
    public static HashMap<String,Object> decode_data(ResultSet resultSet) throws SQLException{
        HashMap<String,Object> map = new HashMap<>();

        int age = resultSet.getInt("age");
        String name = resultSet.getString("name");
        String id = resultSet.getString("id");
        String phone = resultSet.getString("phone");
        float height = resultSet.getFloat("height");
        map.put("age",age);
        map.put("name",name);
        map.put("id",id);
        map.put("phone",phone);
        map.put("height",height);
        return map;
    }
}